<?php

header('Content-type: text/csv');
header('Content-disposition: attachment; filename=' . strtolower($cms_config['cms_name']) . '_shop_' . date('YmdHis') . '.csv;');
#header('Content-type: text/plain');
$output = fopen('php://output', 'w');

fputcsv($output, array(
	'Product code',
	'Parent code',
	'Active?',
	'Path',
	'Title',
	'Categories',
	'Range',
	'Description',
	'Short description',
	'Stock control?',
	'Stock level',
	'Price',
	'Setup price',
	'Minimum quantity',
	'Multiples of',
	'RRP',
	'Cost',
	'VAT rate',
	'Attributes',
	'Simple attributes',
	'Meta',
	'Width',
	'Height',
	'Depth',
	'Weight',
	'Free shipping?',
	'Ships in (min)',
	'Ships in (max)',
	'Related products'
));

$products = $db->fetchAll("SELECT * FROM shop_products WHERE parent_id IS NULL ORDER BY code");

ini_set('display_errors', 'Off');

$root_category_id = $db->fetchOne("SELECT id FROM shop_categories WHERE site_id=? AND parent_id=0", $site['id']);

foreach($products as $product) {
	
	$price_text = '';
	$prices = $db->fetchPairs("SELECT minimum_quantity, price FROM shop_products_prices WHERE product_id=? ORDER BY minimum_quantity", $product['id']);
	foreach($prices as $minimum_quantity => $price) {
		if (count($prices) == 1 and $minimum_quantity == 1) $price_text .= $price;
		else $price_text .= "[{$minimum_quantity}] {$price}\n";
	}
	$price_text = rtrim($price_text);
	
	$categories = array();
	$category_ids = $db->fetchCol("SELECT * FROM shop_categories_products cp WHERE master=1 AND product_id=?", $product['id']);
	foreach($category_ids as $category_id) {
		$category = array();
		$category_chunk = $db->fetchRow("SELECT id, parent_id, title FROM shop_categories WHERE id=?", $category_id);
		while($category_chunk['parent_id'] != $root_category_id) {
			$category[] = $category_chunk['title'];
			$category_chunk = $db->fetchRow("SELECT id, parent_id, title FROM shop_categories WHERE id=?", $category_chunk['parent_id']);
		} $category[] = $category_chunk['title'];
		$categories[] = implode(' | ', array_reverse($category));
	}
	$categories = implode("\n", $categories);
	
	$range = $product['range_id'] ? $db->fetchOne("SELECT title FROM shop_ranges WHERE id=?", $product['range_id']) : '';
	
	$simpleattributes = $db->fetchAll("SELECT psp.*, title, type FROM shop_products_simpleattributes_products psp LEFT JOIN shop_products_simpleattributes ps ON psp.simpleattribute_id=ps.id WHERE product_id=? ORDER BY sort", $product['id']);
	foreach($simpleattributes as $i => $simpleattribute) {
		$simpleattribute['values'] = $db->fetchCol("SELECT CONCAT(value, IF(price, CONCAT('[', price, ']'), '')) FROM shop_products_simpleattributes_values WHERE product_id=? AND simpleattribute_id=? ORDER BY sort", array($product['id'], $simpleattribute['simpleattribute_id']));
		$simpleattributes[$i] = $simpleattribute['title']
		                      . ' |'
		                      . ' type="' . $simpleattribute['type'] . '"'
		                      . ($simpleattribute['default'] ? ' default="' . $simpleattribute['default'] . '"' : '')
		                      . ($simpleattribute['image_prefix'] ? ' image_prefix="' . $simpleattribute['image_prefix'] . '"' : '')
		                      . ($simpleattribute['values'] ? ' values="' . implode(', ', $simpleattribute['values']) . '"' : '');
	}
	
	$metas = implode("\n", $db->fetchCol("SELECT CONCAT(title, ': ', value) FROM shop_products_metas_values pmv LEFT JOIN shop_products_metas pm ON pmv.meta_id=pm.id WHERE product_id=? ORDER BY sort", $product['id']));
	
	$related = implode("\n", $db->fetchCol("SELECT p.code FROM shop_products_related pr JOIN shop_products p ON pr.related_id=p.id WHERE product_id=?", $product['id']));
	
	fputcsv($output, array(
		preg_match('/^0\d+$/', $product['code']) ? "#{$product['code']}" : $product['code'],
		'',
		$product['active'],
		$product['path'],
		$product['title'],
		$categories,
		$range,
		$product['description'],
		$product['short_description'],
		$product['stock_control'],
		$product['stock_level'],
		$price_text,
		$product['setup_price'],
		$product['minimum_quantity'],
		$product['multiples_of'],
		$product['rrp'],
		$product['cost'],
		$product['vat_rate'],
		'',
		implode("\n", $simpleattributes),
		$metas,
		$product['width'],
		$product['height'],
		$product['depth'],
		$product['weight'],
		$product['free_shipping'],
		$product['ships_in_min'],
		$product['ships_in_max'],
		$related
	));
	
	$subproducts = $db->fetchAll("SELECT * FROM shop_products WHERE parent_id=? ORDER BY code", $product['id']);
	foreach($subproducts as $subproduct) {
		
		$price_text = '';
		$prices = $db->fetchPairs("SELECT minimum_quantity, price FROM shop_products_prices WHERE product_id=? ORDER BY minimum_quantity", $subproduct['id']);
		foreach($prices as $minimum_quantity => $price) {
			if (count($prices) == 1 and $minimum_quantity == 1) $price_text .= $price;
			else $price_text .= "[{$minimum_quantity}] {$price}\n";
		}
		$price_text = rtrim($price_text);
		
		$attributes = implode("\n", $db->fetchCol("SELECT CONCAT(title, ': ', value) FROM shop_products_attributes_products pap JOIN shop_products_attributes pa ON pap.attribute_id=pa.id JOIN shop_products_attributes_values pav ON pav.attribute_id=pap.attribute_id WHERE pap.product_id=? AND pav.product_id=? ORDER BY pap.sort", array($product['id'], $subproduct['id'])));
		
		$simpleattributes = $db->fetchAll("SELECT psp.*, title, type FROM shop_products_simpleattributes_products psp LEFT JOIN shop_products_simpleattributes ps ON psp.simpleattribute_id=ps.id WHERE product_id=? ORDER BY sort", $subproduct['id']);
		foreach($simpleattributes as $i => $simpleattribute) {
			$simpleattribute['values'] = $db->fetchCol("SELECT CONCAT(value, IF(price, CONCAT('[', price, ']'), '')) FROM shop_products_simpleattributes_values WHERE product_id=? AND simpleattribute_id=? ORDER BY sort", array($subproduct['id'], $simpleattribute['simpleattribute_id']));
			$simpleattributes[$i] = $simpleattribute['title']
			                      . ' |'
			                      . ' type="' . $simpleattribute['type'] . '"'
			                      . ($simpleattribute['default'] ? ' default="' . $simpleattribute['default'] . '"' : '')
			                      . ($simpleattribute['image_prefix'] ? ' image_prefix="' . $simpleattribute['image_prefix'] . '"' : '')
			                      . ($simpleattribute['values'] ? ' values="' . implode(', ', $simpleattribute['values']) . '"' : '');
		}
		
		fputcsv($output, array(
			preg_match('/^0\d+$/', $subproduct['code']) ? "#{$subproduct['code']}" : $subproduct['code'],
			preg_match('/^0\d+$/', $product['code']) ? "#{$product['code']}" : $product['code'],
			'',
			'',
			'',
			'',
			'',
			'',
			'',
			$subproduct['stock_control'],
			$subproduct['stock_level'],
			$price_text,
			$subproduct['setup_price'],
			$subproduct['minimum_quantity'],
			$subproduct['multiples_of'],
			$subproduct['rrp'],
			$subproduct['cost'],
			$subproduct['vat_rate'],
			$attributes,
			implode("\n", $simpleattributes),
			'',
			$subproduct['width'],
			$subproduct['height'],
			$subproduct['depth'],
			$subproduct['weight'],
			$subproduct['free_shipping'],
			$subproduct['ships_in_min'],
			$subproduct['ships_in_max'],
			''
		));
		
	}
	
}
